-- DBTools Manager Professional (Enterprise Edition)
-- Database Dump for: utility
-- Backup Generated in: 10/11/2011 10:38:59 AM
-- Database Server Version: MySQL 5.1.33

-- USEGO

SET FOREIGN_KEY_CHECKS=0;
-- GO


--
-- Dumping Tables
--

--
-- Table: billing
--
CREATE TABLE `billing` 
(
	`id` integer (11) NOT NULL AUTO_INCREMENT , 
	`station_id` integer (11), 
	`building_id` integer (11), 
	`occupant_id` integer (11), 
	`consumer_no` varchar (50), 
	`billing_date` date, 
	`previous_reading` varchar (10), 
	`present_reading` varchar (10), 
	`units_consumed` varchar (10), 
	`units_free` varchar (10), 
	`units_total` varchar (10), 
	`water_charges` varchar (5), 
	`tv_charges` varchar (5), 
	`split_charges` varchar (10), 
	`window_charges` varchar (10), 
	`furniture_charges` varchar (10), 
	`rebate_rate` varchar (5), 
	`meter_rent` varchar (5), 
	`grand_total` varchar (10), 
	`print_status` varchar (10), 
	`payment_status` varchar (10), 
	`bill_type` varchar (10), 
	`status` varchar (10), 
	`created_at` date, 
	`updated_at` date,
	PRIMARY KEY (`id`)
) TYPE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
-- GO

--
-- Dumping Table Data: billing
--
BEGIN;
-- GO
INSERT INTO `billing` (`id`, `station_id`, `building_id`, `occupant_id`, `consumer_no`, `billing_date`, `previous_reading`, `present_reading`, `units_consumed`, `units_free`, `units_total`, `water_charges`, `tv_charges`, `split_charges`, `window_charges`, `furniture_charges`, `rebate_rate`, `meter_rent`, `grand_total`, `print_status`, `payment_status`, `bill_type`, `status`, `created_at`, `updated_at`) VALUES(1, 1, 7, 2, '001', '2011-09-10', '26059', '26704', '645', NULL, '6176', '40', '35', '0', '0', '', NULL, '18', '6269', '0', '0', 'officer', '1', '2011-10-10', '2011-10-10');
-- GO
COMMIT;
-- GO

--
-- Index: FK_billing_station
--
ALTER TABLE `utility`.`billing` ADD INDEX `FK_billing_station` (`station_id` );
-- GO

--
-- Index: FK_billing_building
--
ALTER TABLE `utility`.`billing` ADD INDEX `FK_billing_building` (`building_id` );
-- GO

--
-- Index: FK_billing_occupant
--
ALTER TABLE `utility`.`billing` ADD INDEX `FK_billing_occupant` (`occupant_id` );
-- GO

--
-- Table: building
--
CREATE TABLE `building` 
(
	`id` integer (11) NOT NULL AUTO_INCREMENT , 
	`station_id` integer (11), 
	`building_no` varchar (50), 
	`meter_no` varchar (50), 
	`meter_phase` varchar (10), 
	`consumer_no` varchar (50), 
	`status` varchar (10), 
	`created_at` date, 
	`updated_at` date,
	PRIMARY KEY (`id`)
) TYPE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
-- GO

--
-- Dumping Table Data: building
--
BEGIN;
-- GO
INSERT INTO `building` (`id`, `station_id`, `building_no`, `meter_no`, `meter_phase`, `consumer_no`, `status`, `created_at`, `updated_at`) VALUES(1, 1, 'MOQ 1/2', '23154', '3 Phase', '4AK Bde / 001', '1', '2011-09-05', '2011-09-29');
-- GO
INSERT INTO `building` (`id`, `station_id`, `building_no`, `meter_no`, `meter_phase`, `consumer_no`, `status`, `created_at`, `updated_at`) VALUES(2, 1, 'MOQ 1/1', '23153', '3 Phase', '4AK Bde / 002', '1', '2011-09-06', '2011-10-08');
-- GO
INSERT INTO `building` (`id`, `station_id`, `building_no`, `meter_no`, `meter_phase`, `consumer_no`, `status`, `created_at`, `updated_at`) VALUES(3, 1, 'MOQ 14/1', '2154', '1 Phase', '6AK Bde / 003', '1', '2011-09-29', '2011-09-29');
-- GO
INSERT INTO `building` (`id`, `station_id`, `building_no`, `meter_no`, `meter_phase`, `consumer_no`, `status`, `created_at`, `updated_at`) VALUES(4, 1, 'Soldier Quarter', '090202', '1 Phase', 'E-1', '1', '2011-10-08', '2011-10-08');
-- GO
INSERT INTO `building` (`id`, `station_id`, `building_no`, `meter_no`, `meter_phase`, `consumer_no`, `status`, `created_at`, `updated_at`) VALUES(5, 3, 'MOQ A-1', '12512', '3 Phase', 'JPJ A-1 / 001', '1', '2011-10-08', '2011-10-08');
-- GO
INSERT INTO `building` (`id`, `station_id`, `building_no`, `meter_no`, `meter_phase`, `consumer_no`, `status`, `created_at`, `updated_at`) VALUES(6, 3, 'MOQ A-2', '52415', '1 Phase', 'JPJ A-2 / 002', '1', '2011-10-08', '2011-10-08');
-- GO
INSERT INTO `building` (`id`, `station_id`, `building_no`, `meter_no`, `meter_phase`, `consumer_no`, `status`, `created_at`, `updated_at`) VALUES(7, 1, 'BK 14 Set 1', '050414', '3 Phase', '001', '1', '2011-10-10', '2011-10-10');
-- GO
COMMIT;
-- GO

--
-- Index: FK_building_station
--
ALTER TABLE `utility`.`building` ADD INDEX `FK_building_station` (`station_id` );
-- GO

--
-- Table: calc_value
--
CREATE TABLE `calc_value` 
(
	`id` integer (11) NOT NULL AUTO_INCREMENT , 
	`title` varchar (50), 
	`value` float (13,3), 
	`remarks` varchar (500), 
	`status` varchar (10), 
	`created_at` date, 
	`updated_at` date,
	PRIMARY KEY (`id`)
) TYPE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
-- GO

--
-- Dumping Table Data: calc_value
--
BEGIN;
-- GO
INSERT INTO `calc_value` (`id`, `title`, `value`, `remarks`, `status`, `created_at`, `updated_at`) VALUES(1, 'jco_winter', 30.000, 'jco_winter are the number of Free Units authorized to JCOs in Winter Season. Please give number of Free Units authorized in \"Numeric Value\" box.', '1', '2011-09-20', '2011-09-20');
-- GO
INSERT INTO `calc_value` (`id`, `title`, `value`, `remarks`, `status`, `created_at`, `updated_at`) VALUES(2, 'jco_summer', 50.000, 'jco_summer are the number of Free Units authorized to JCOs in Summer Season. Please give number of Free Units authorized in \"Numeric Value\" box.', '1', '2011-09-20', '2011-09-20');
-- GO
INSERT INTO `calc_value` (`id`, `title`, `value`, `remarks`, `status`, `created_at`, `updated_at`) VALUES(3, 'soldier_summer', 18.000, 'soldier_summer are the number of Free Units authorized to Soldiers/ORs in Summer Season. Please give number of Free Units authorized in \"Numeric Value\" box.', '1', '2011-09-20', '2011-09-20');
-- GO
INSERT INTO `calc_value` (`id`, `title`, `value`, `remarks`, `status`, `created_at`, `updated_at`) VALUES(4, 'soldier_winter', 23.000, 'soldier_winter are the number of Free Units authorized to Soldiers/ORs in Winter Season. Please give number of Free Units authorized in \"Numeric Value\" box.', '1', '2011-09-20', '2011-09-20');
-- GO
INSERT INTO `calc_value` (`id`, `title`, `value`, `remarks`, `status`, `created_at`, `updated_at`) VALUES(5, 'water_charges', 40.000, 'It  is the amount in Rs. for Water Charges to be added in every bill. Value can be given in Paisa too. Example 43.50', '1', '2011-09-20', '2011-09-20');
-- GO
INSERT INTO `calc_value` (`id`, `title`, `value`, `remarks`, `status`, `created_at`, `updated_at`) VALUES(6, 'tv_charges', 35.000, 'It  is the amount in Rs. for TV Charges to be added in every bill. Value can be given in Paisa too. Example 43.50', '1', '2011-09-20', '2011-09-20');
-- GO
INSERT INTO `calc_value` (`id`, `title`, `value`, `remarks`, `status`, `created_at`, `updated_at`) VALUES(7, 'single_phase_min', 75.000, 'It is the minimum amount of Electricity Bill for Single Phase Meter. Value can be given in Paisa too. Example 43.50', '1', '2011-09-20', '2011-09-20');
-- GO
INSERT INTO `calc_value` (`id`, `title`, `value`, `remarks`, `status`, `created_at`, `updated_at`) VALUES(8, 'three_phase_min', 150.000, 'It is the minimum amount of Electricity Bill for Three Phase Meter. Value can be given in Paisa too. Example 43.50', '1', '2011-09-20', '2011-09-20');
-- GO
INSERT INTO `calc_value` (`id`, `title`, `value`, `remarks`, `status`, `created_at`, `updated_at`) VALUES(9, 'single_phase_rent', 12.000, 'This is the Meter Rent for Single Phase Meter.', '1', '2011-09-20', '2011-09-20');
-- GO
INSERT INTO `calc_value` (`id`, `title`, `value`, `remarks`, `status`, `created_at`, `updated_at`) VALUES(10, 'three_phase_rent', 18.000, 'This is the Meter Rent for Three Phase Meter.', '1', '2011-09-20', '2011-09-20');
-- GO
INSERT INTO `calc_value` (`id`, `title`, `value`, `remarks`, `status`, `created_at`, `updated_at`) VALUES(11, 'commercial_rate', 16.817, 'Commercial Rate is applicable to commercial customers only. Please give the final value including GST & Income Tax.', '1', '2011-09-20', '2011-09-20');
-- GO
INSERT INTO `calc_value` (`id`, `title`, `value`, `remarks`, `status`, `created_at`, `updated_at`) VALUES(12, 'split_ac_units', 480.000, 'This is the number of electricity units for Direct Split AC. Only give number of units to be charged in the bill.', '1', '2011-09-29', '2011-09-29');
-- GO
INSERT INTO `calc_value` (`id`, `title`, `value`, `remarks`, `status`, `created_at`, `updated_at`) VALUES(13, 'window_ac_units', 720.000, 'This is the number of electricity units for Direct Window AC. Only give number of units to be charged in the bill.', '1', '2011-09-29', '2011-09-29');
-- GO
COMMIT;
-- GO

--
-- Table: elec_tarrif
--
CREATE TABLE `elec_tarrif` 
(
	`id` integer (11) NOT NULL AUTO_INCREMENT , 
	`title` varchar (50), 
	`from_unit` integer (11), 
	`to_unit` integer (11), 
	`civil_rate` float (13,3), 
	`rebate_rate` float (13,3), 
	`officer_rate` float (13,3), 
	`status` varchar (10), 
	`created_at` date, 
	`updated_at` date,
	PRIMARY KEY (`id`)
) TYPE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
-- GO

--
-- Dumping Table Data: elec_tarrif
--
BEGIN;
-- GO
INSERT INTO `elec_tarrif` (`id`, `title`, `from_unit`, `to_unit`, `civil_rate`, `rebate_rate`, `officer_rate`, `status`, `created_at`, `updated_at`) VALUES(1, '1 to 50', 1, 50, 1.970, 0.210, 1.760, '1', '2011-09-06', '2011-09-20');
-- GO
INSERT INTO `elec_tarrif` (`id`, `title`, `from_unit`, `to_unit`, `civil_rate`, `rebate_rate`, `officer_rate`, `status`, `created_at`, `updated_at`) VALUES(2, '1 to 100', 1, 100, 5.411, 0.210, 5.201, '1', '2011-09-06', '2011-09-20');
-- GO
INSERT INTO `elec_tarrif` (`id`, `title`, `from_unit`, `to_unit`, `civil_rate`, `rebate_rate`, `officer_rate`, `status`, `created_at`, `updated_at`) VALUES(3, '101 to 300', 101, 300, 8.126, 0.290, 7.836, '1', '2011-09-20', '2011-09-20');
-- GO
INSERT INTO `elec_tarrif` (`id`, `title`, `from_unit`, `to_unit`, `civil_rate`, `rebate_rate`, `officer_rate`, `status`, `created_at`, `updated_at`) VALUES(4, '300 to 700', 301, 700, 12.560, 0.710, 11.850, '1', '2011-09-20', '2011-09-27');
-- GO
INSERT INTO `elec_tarrif` (`id`, `title`, `from_unit`, `to_unit`, `civil_rate`, `rebate_rate`, `officer_rate`, `status`, `created_at`, `updated_at`) VALUES(5, '701 and Above', 701, 10000, 15.649, 0.890, 14.759, '1', '2011-09-20', '2011-09-27');
-- GO
COMMIT;
-- GO

--
-- Table: occupant
--
CREATE TABLE `occupant` 
(
	`id` integer (11) NOT NULL AUTO_INCREMENT , 
	`station_id` integer (11), 
	`building_id` integer (11), 
	`army_no` varchar (20), 
	`rank` varchar (30), 
	`name` varchar (100), 
	`unit` varchar (100), 
	`date_occupied` date, 
	`date_vacated` date, 
	`occupied_status` varchar (10), 
	`status` varchar (10), 
	`created_at` date, 
	`updated_at` date,
	PRIMARY KEY (`id`)
) TYPE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
-- GO

--
-- Dumping Table Data: occupant
--
BEGIN;
-- GO
INSERT INTO `occupant` (`id`, `station_id`, `building_id`, `army_no`, `rank`, `name`, `unit`, `date_occupied`, `date_vacated`, `occupied_status`, `status`, `created_at`, `updated_at`) VALUES(2, 1, 7, 'PA-33503', 'Lt Col', 'Naveed Ahmad', '641 Mjd Bn', '2011-08-15', NULL, '1', '1', '2011-10-10', '2011-10-10');
-- GO
INSERT INTO `occupant` (`id`, `station_id`, `building_id`, `army_no`, `rank`, `name`, `unit`, `date_occupied`, `date_vacated`, `occupied_status`, `status`, `created_at`, `updated_at`) VALUES(3, 1, 2, 'PA-33552', 'Maj', 'Raees Khan', '39 Punjab Regt', '2011-10-10', '2011-10-10', '0', '1', '2011-10-10', '2011-10-10');
-- GO
COMMIT;
-- GO

--
-- Index: FK_occupant_building
--
ALTER TABLE `utility`.`occupant` ADD INDEX `FK_occupant_building` (`building_id` );
-- GO

--
-- Index: FK_occupant_station
--
ALTER TABLE `utility`.`occupant` ADD INDEX `FK_occupant_station` (`station_id` );
-- GO

--
-- Table: role
--
CREATE TABLE `role` 
(
	`id` integer (11) NOT NULL AUTO_INCREMENT , 
	`title` varchar (50), 
	`status` varchar (50), 
	`created_at` date, 
	`updated_at` date,
	PRIMARY KEY (`id`)
) TYPE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
-- GO

--
-- Dumping Table Data: role
--
BEGIN;
-- GO
COMMIT;
-- GO

--
-- Table: station
--
CREATE TABLE `station` 
(
	`id` integer (11) NOT NULL AUTO_INCREMENT , 
	`title` varchar (100), 
	`status` varchar (10),
	PRIMARY KEY (`id`)
) TYPE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
-- GO

--
-- Dumping Table Data: station
--
BEGIN;
-- GO
INSERT INTO `station` (`id`, `title`, `status`) VALUES(1, 'Bhimber', '1');
-- GO
INSERT INTO `station` (`id`, `title`, `status`) VALUES(2, 'Kotli', '1');
-- GO
INSERT INTO `station` (`id`, `title`, `status`) VALUES(3, 'Jalal Pur  Jattan', '1');
-- GO
COMMIT;
-- GO

--
-- Table: user
--
CREATE TABLE `user` 
(
	`id` integer (11) NOT NULL AUTO_INCREMENT , 
	`name` varchar (100), 
	`username` varchar (50), 
	`password` varchar (50), 
	`status` varchar (10), 
	`created_at` date, 
	`updated_at` date,
	PRIMARY KEY (`id`)
) TYPE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
-- GO

--
-- Dumping Table Data: user
--
BEGIN;
-- GO
INSERT INTO `user` (`id`, `name`, `username`, `password`, `status`, `created_at`, `updated_at`) VALUES(1, 'Kashif Hussain', 'kashifhussain', '4e075844d2e00e4c800c8c62716bed8c', '1', '2011-09-16', '2011-09-16');
-- GO
COMMIT;
-- GO

--
-- Dumping Tables Foreign Keys
--

--
-- Foreign Key Constraint: FK_billing_building
--
ALTER TABLE `billing` ADD CONSTRAINT `FK_billing_building` FOREIGN KEY (`building_id`) REFERENCES `building`(`id`) ON UPDATE CASCADE;
-- GO

--
-- Foreign Key Constraint: FK_billing_occupant
--
ALTER TABLE `billing` ADD CONSTRAINT `FK_billing_occupant` FOREIGN KEY (`occupant_id`) REFERENCES `occupant`(`id`) ON UPDATE CASCADE;
-- GO

--
-- Foreign Key Constraint: FK_billing_station
--
ALTER TABLE `billing` ADD CONSTRAINT `FK_billing_station` FOREIGN KEY (`station_id`) REFERENCES `station`(`id`) ON UPDATE CASCADE;
-- GO

--
-- Foreign Key Constraint: FK_building_station
--
ALTER TABLE `building` ADD CONSTRAINT `FK_building_station` FOREIGN KEY (`station_id`) REFERENCES `station`(`id`) ON UPDATE CASCADE;
-- GO

--
-- Foreign Key Constraint: FK_occupant_building
--
ALTER TABLE `occupant` ADD CONSTRAINT `FK_occupant_building` FOREIGN KEY (`building_id`) REFERENCES `building`(`id`) ON UPDATE CASCADE;
-- GO

--
-- Foreign Key Constraint: FK_occupant_station
--
ALTER TABLE `occupant` ADD CONSTRAINT `FK_occupant_station` FOREIGN KEY (`station_id`) REFERENCES `station`(`id`) ON UPDATE CASCADE;
-- GO

--
-- Dumping Triggers
--
SET FOREIGN_KEY_CHECKS=1;
-- GO

